﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using RRHH.Entities;
using System.Data.SqlClient;
using System.Data;
using System.Messaging;


namespace RRHH.DataAccess
{
    public class DAOContrato : IDAOBase<BEContrato,int>
    {

    
        public Boolean Update(BEContrato oContrato)
        {

            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append(" UPDATE CONTRATO SET ");

            string sql = sqlBuilder.ToString();

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = sql;

            sqlCommand.Parameters.Add("@IDCONTRATO", SqlDbType.Int);
            sqlCommand.Parameters.Add("@FECHAINICIO", SqlDbType.DateTime);
            sqlCommand.Parameters.Add("@FECHAFIN", SqlDbType.DateTime);
            sqlCommand.Parameters.Add("@IDTIPOEXTINCION", SqlDbType.NVarChar);
            sqlCommand.Parameters.Add("@CODIGOEMPLEADO", SqlDbType.NVarChar);
            sqlCommand.Parameters.Add("@IDPERSONAL", SqlDbType.Int);
            sqlCommand.Parameters.Add("@SALARIORECIBO", SqlDbType.Decimal);
            sqlCommand.Parameters.Add("@IDCARGO", SqlDbType.NVarChar);
            sqlCommand.Parameters.Add("@IDESTADO", SqlDbType.Int);
            sqlCommand.Parameters.Add("@FECHACREACION", SqlDbType.DateTime);
            sqlCommand.Parameters.Add("@VIGENTE", SqlDbType.Int);
            sqlCommand.Parameters.Add("@IDTIPOCONTRATO", SqlDbType.NVarChar);
            sqlCommand.Parameters.Add("@FLAGCESADO", SqlDbType.NVarChar);
            sqlCommand.Parameters.Add("@FECHAEXTINCION", SqlDbType.DateTime);
            sqlCommand.Parameters.Add("@ESTADO", SqlDbType.Char);

      
            using (DataAccess adoDataService = new DataAccess())
            {
                adoDataService.BeginTransaction();
                adoDataService.ExecuteNonQuery(sqlCommand);
                adoDataService.CommitTransaction();
                adoDataService.Dispose();

            }

            return true;

        }

   
        public BEContrato Create(BEContrato oContrato)
        {
            try
            {
                               
                SqlCommand sqlCommand = new SqlCommand("SP_REGISTRAR_CONTRATO");
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.Parameters.Add(new SqlParameter("@FECHAINICIO", oContrato.FECHAINICIO));
                sqlCommand.Parameters.Add(new SqlParameter("@FECHAFIN", oContrato.FECHAFIN));
                sqlCommand.Parameters.Add(new SqlParameter("@CODIGOEMPLEADO", oContrato.CODIGOEMPLEADO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDPERSONAL", oContrato.IDPERSONAL));
                sqlCommand.Parameters.Add(new SqlParameter("@SALARIORECIBO", oContrato.SALARIORECIBO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDCARGO", oContrato.IDCARGO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDESTADO", oContrato.IDESTADO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDTIPOCONTRATO", oContrato.IDTIPOCONTRATO));

                using (DataAccess adoDataService = new DataAccess())
                {
                    adoDataService.BeginTransaction();
                    Int32 orderID = adoDataService.ExecuteScalar(sqlCommand);
                    adoDataService.CommitTransaction();
                    adoDataService.Dispose();
        
                    oContrato.IDCONTRATO = orderID;
                }

               
            }
            catch (Exception ex)
            {
                return null;
            }


            return oContrato;

        }

        public BEContrato Get(int contratoID)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.Append(" SELECT * FROM CONTRATO  ");
            sqlBuilder.Append(" WHERE IDCONTRATO = @IDCONTRATO ");

            string sql = sqlBuilder.ToString();

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Parameters.Add("@IDCONTRATO", SqlDbType.Int);
            sqlCommand.Parameters["@IDCONTRATO"].Value = contratoID;

            sqlCommand.CommandText = sql;

            using (DataAccess adoDataService = new DataAccess())
            {
                BEContrato oContrato = new BEContrato();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                if (dr.Read())
                {
                    oContrato.IDCONTRATO = Convert.ToInt32(dr["IDCONTRATO"]);
                    oContrato.FECHAINICIO = Convert.ToDateTime(dr["FECHAINICIO"]);
                    oContrato.FECHAFIN = Convert.ToDateTime(dr["FECHAFIN"]);
                    oContrato.IDTIPOEXTINCION = Convert.ToString(dr["IDTIPOEXTINCION"]);
                    oContrato.CODIGOEMPLEADO = Convert.ToString(dr["CODIGOEMPLEADO"]);
                    oContrato.IDPERSONAL = Convert.ToInt32(dr["IDPERSONAL"]);
                    oContrato.SALARIORECIBO = Convert.ToDecimal(dr["SALARIORECIBO"]);
                    oContrato.IDCARGO = Convert.ToInt32(dr["IDCARGO"]);
                    oContrato.IDESTADO = Convert.ToInt32(dr["IDESTADO"]);
                    oContrato.FECHACREACION = Convert.ToDateTime(dr["FECHACREACION"]);
                    oContrato.VIGENTE = Convert.ToInt32(dr["VIGENTE"]);
                    oContrato.IDTIPOCONTRATO = Convert.ToString(dr["IDTIPOCONTRATO"]);
                    oContrato.FLAGCESADO = Convert.ToString(dr["FLAGCESADO"]);
                    oContrato.FECHAEXTINCION = Convert.ToDateTime(dr["FECHAEXTINCION"]);
                    oContrato.ESTADO = Convert.ToString(dr["ESTADO"]);

                    dr.Close();

                    adoDataService.Dispose();


                }
                return oContrato;
            }
        }

        public bool Delete(int contratoID)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.Append(" DELETE FROM CONTRATO  ");
            sqlBuilder.Append(" WHERE IDCONTRATO = @IDCONTRATO ");

            string sql = sqlBuilder.ToString();

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Parameters.Add("@IDCONTRATO", SqlDbType.Int);
            sqlCommand.Parameters["@IDCONTRATO"].Value = contratoID;

            sqlCommand.CommandText = sql;

            using (DataAccess adoDataService = new DataAccess())
            {
                adoDataService.BeginTransaction();
                adoDataService.ExecuteNonQuery(sqlCommand);
                adoDataService.CommitTransaction();
                adoDataService.Dispose();

            }

            return true;

        }


        public List<BEContrato> List(int personalID)
        {
         
            string sql = "SP_LISTAR_CONTRATO";

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Parameters.Add("@IDPERSONAL", SqlDbType.Int).Value = personalID;
            sqlCommand.CommandText = sql;
            sqlCommand.CommandType = CommandType.StoredProcedure;
           

            using (DataAccess adoDataService = new DataAccess())
            {

                List<BEContrato> oContratoList = new List<BEContrato>();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                while (dr.Read())
                {
                    BEContrato oContrato = new BEContrato();

                    oContrato.IDCONTRATO = Convert.ToInt32(dr["IDCONTRATO"]);
                    oContrato.FECHAINICIO = Convert.ToDateTime(dr["FECHAINICIO"]);
                    oContrato.FECHAFIN = Convert.ToDateTime(dr["FECHAFIN"]);
                    //oContrato.IDTIPOEXTINCION = Convert.ToString(dr["IDTIPOEXTINCION"]);
                    //oContrato.CODIGOEMPLEADO = Convert.ToString(dr["CODIGOEMPLEADO"]);
                    oContrato.IDPERSONAL = Convert.ToInt32(dr["IDPERSONAL"]);
                    oContrato.SALARIORECIBO = Convert.ToDecimal(dr["SALARIORECIBO"]);
                    //oContrato.IDCARGO = Convert.ToInt32(dr["IDCARGO"]);
                    //oContrato.IDESTADO = Convert.ToInt32(dr["IDESTADO"]);
                    //oContrato.FECHACREACION = Convert.ToDateTime(dr["FECHACREACION"]);
                    //oContrato.VIGENTE = Convert.ToInt32(dr["VIGENTE"]);
                    //oContrato.IDTIPOCONTRATO = Convert.ToString(dr["IDTIPOCONTRATO"]);
                    //oContrato.FLAGCESADO = Convert.ToString(dr["FLAGCESADO"]);
                    //oContrato.FECHAEXTINCION = Convert.ToDateTime(dr["FECHAEXTINCION"]);
                    //oContrato.ESTADO = Convert.ToString(dr["ESTADO"]);

                    oContratoList.Add(oContrato);
                }
                dr.Close();

                adoDataService.Dispose();

                return oContratoList;
            }

        }


        //-------------------------------------------------------

        public List<BEContrato> GestContratosVencidos()
        {
            List<BEContrato> lstVencidos = new List<BEContrato>();
            try
            {

                string rutaColaIn = @".\private$\ContratosVen";
                if (!MessageQueue.Exists(rutaColaIn))
                    MessageQueue.Create(rutaColaIn);
                MessageQueue colaIn = new MessageQueue(rutaColaIn);

                int cantCola = colaIn.GetAllMessages().Count();
                colaIn.Formatter = new XmlMessageFormatter(new Type[] { typeof(BEContrato) });

                if (cantCola > 0)
                {
                    for (int i = 0; i < cantCola; i++)
                    {
                        Message mensajeIn = colaIn.Receive();
                        BEContrato bean = (BEContrato)mensajeIn.Body;
                        lstVencidos.Add(bean);
                    }
                }

            }
            catch (Exception ex)
            {                
                throw;
            }

            return lstVencidos;
        }
        //-------------------------------------------------------



        List<BEContrato> IDAOBase<BEContrato, int>.List()
        {
            throw new NotImplementedException();
        }
    }
}